/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.dal;

import edu.uoc.tdp.entity.Space;
import edu.uoc.tdp.customException.EIssueException;

import edu.uoc.tdp.i18n.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
  
/**
 * @author J2RS / Jordi Sanchez
 */
public class ManagementSpaces {

   private ManagementDb mngBD = null;
   private Connection conexion = null;

    public ManagementSpaces() throws EIssueException {
        mngBD = new ManagementDb();
        try {
            mngBD.connectDB();
        } catch (edu.uoc.tdp.common.EIssueException ex) {
            Logger.getLogger(ManagementSpaces.class.getName()).log(Level.SEVERE, null, ex);
        }
        conexion = mngBD.getConnection();
    }

    public ManagementSpaces(ManagementDb mngDb) {
        mngBD = mngDb;
        conexion = mngDb.getConnection();
    }

    /**
     * Mètode que obte la lista de espais - taula spaces
     * @return Lista de espais
     */
    public List<Space> GetAllSpaces() throws EIssueException{
        List<Space> listSpace = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT spaceid, spacedescription, capacity, price_hour, price_day, url_map, how_to_get, postal_code, city, address, creation_date, creation_user, inactivation_date, inactivation_user, record_date  FROM spaces";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listSpace.add(new Space(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getString(10),rs.getDate(11),rs.getString(12),rs.getDate(13),rs.getString(14),rs.getDate(15) ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listSpace;
    }

    /**
     * Mètode que obte la lista de espais - taula spaces
     * @return Lista de espais
     */
    public List<Space> GetAllSpacesTable() throws EIssueException{
        List<Space> listSpace = new ArrayList();

       try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT spaceid, spacedescription, capacity, price_hour, price_day, postal_code FROM spaces";
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listSpace.add(new Space(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),"","",rs.getInt(6),"","",null,"",null,"",null ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        return listSpace;
    }
    
    /**
     * Mètode que obte un espai - taula spaces
     * @return Lista de espai
     */
    public List<Space> GetOneSpace(int SpaceId) throws EIssueException {
        List<Space> listSpace = new ArrayList();
        
        try {
            Statement command = this.mngBD.createCommand();
            String select = "SELECT spaceid, spacedescription, capacity, price_hour, price_day, url_map, how_to_get, postal_code, city, address, creation_date, creation_user, inactivation_date, inactivation_user, record_date  FROM spaces WHERE spaceid=" + String.valueOf(SpaceId);
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                listSpace.add(new Space(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),rs.getString(6),rs.getString(7),rs.getInt(8),rs.getString(9),rs.getString(10),rs.getDate(11),rs.getString(12),rs.getDate(13),rs.getString(14),rs.getDate(15) ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listSpace;
    }
    
    /**
     * Mètode comprova si existeix el registre i que obte un boolean - taula spaces
     * @return boolean
     */
    public boolean CheckExistsSpace(int SpaceId) throws EIssueException {
        
        boolean Result=false;
        try {           
                        
            Statement command = this.mngBD.createCommand();
            String select = "SELECT spaceid FROM spaces WHERE spaceid=" + String.valueOf(SpaceId);
            ResultSet rs = command.executeQuery(select);
            while (rs.next()) {
                 Result=true;        
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return Result;
    }
    
    /**
     * Mètode que obte una llista de Espais, filtrats per Descricio - taula Spaces
     * @return Lista de espais
     */
    public List<Space> GetSpace_FindByDesc(String Desc) throws EIssueException {
        List<Space> listSpace = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT spaceid, spacedescription, capacity, price_hour, price_day, postal_code FROM spaces WHERE spacedescription like '%" + Desc + "%'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listSpace.add(new Space(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),"","",rs.getInt(6),"","",null,"",null,"",null ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listSpace;
    }  
    
    
    /**
     * Mètode que obte una llista de Espais, filtrats per Codi Postal - taula Spaces
     * @return Lista de espais
     */
    public List<Space> GetSpace_FindByPostalCode(Integer PostalCode) throws EIssueException {
        List<Space> listSpace = new ArrayList();
        
        try {          
            Statement command = this.mngBD.createCommand();
            String select = "SELECT spaceid, spacedescription, capacity, price_hour, price_day, postal_code FROM spaces WHERE postal_code='" + String.valueOf(PostalCode) + "'";
            ResultSet rs = command.executeQuery(select);
            
            while (rs.next()) {
                listSpace.add(new Space(rs.getInt(1), rs.getString(2), rs.getInt(3),rs.getDouble(4),rs.getDouble(5),"","",rs.getInt(6),"","",null,"",null,"",null ));
                // El primer index en rs es el 1
            }
            rs.close();
            command.close();

        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_STOP));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return listSpace;
    }  
    
    
    /**
     * Mètode que guarda dades (Update) un espai - taula spaces
     * @return boolea
     */    
    public boolean SaveSpace(Space Space) throws EIssueException{
        boolean PROCESS_STATE = false;
        try {
            String insertQuery = "INSERT INTO spaces(spaceid, spacedescription, capacity, price_hour, price_day, url_map, how_to_get, postal_code, city, address, creation_date, creation_user, record_date) VALUES (?,?,?,?,?,?,?,?,?,?,now(),?,now())";
            PreparedStatement command = this.mngBD.prepareCommand(insertQuery);
            command.setInt(1, Space.getSpaceId());
            command.setString(2, Space.getSpaceDescription());
            command.setInt(3, Space.getCapacity());
            command.setDouble(4, Space.getPriceHour());
            command.setDouble(5, Space.getPriceDay());
            command.setString(6, Space.getUrl_map());
            command.setString(7, Space.getHowToGet());
            command.setInt(8, Space.getPostalCode());
            command.setString(9, Space.getCity());
            command.setString(10, Space.getAddress());
            command.setString(11, Space.getCreationUser());            
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }
        
        return PROCESS_STATE;
    }

    /**
     * Mètode que fa Update de un espai - taula spaces
     * @return boolea
     */    
    public boolean SaveSpaceChanges(Space Space) throws EIssueException{
        boolean PROCESS_STATE = false;
        
        try {
            String updateQuery = "UPDATE spaces SET spacedescription=?, capacity=?, price_hour=?, price_day=?, url_map=?, how_to_get=?, postal_code=?, city=?, address=?  WHERE spaceid=?";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);            
            command.setString(1, Space.getSpaceDescription());
            command.setInt(2, Space.getCapacity());
            command.setDouble(3, Space.getPriceHour());
            command.setDouble(4, Space.getPriceDay());
            command.setString(5, Space.getUrl_map());
            command.setString(6, Space.getHowToGet());
            command.setInt(7, Space.getPostalCode());
            command.setString(8, Space.getCity());
            command.setString(9, Space.getAddress());
            command.setInt(10, Space.getSpaceId());
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

    /**
     * Mètode que fa dona de baixa/desactiva un espai - taula spaces
     * @return boolea
     */     
    public boolean SaveInactivationSpace(int spaceId, String userName) throws EIssueException{
        boolean PROCESS_STATE = false;
        
        try {
            String updateQuery = "UPDATE spaces SET inactivation_date=now(), inactivation_user=?  WHERE spaceid=?";
            PreparedStatement command = this.mngBD.prepareCommand(updateQuery);
            command.setString(1, userName);
            command.setInt(2, spaceId);
            command.execute();
            command.close();
            PROCESS_STATE = true;
        } catch (SQLException sqle) {
            System.out.println(sqle.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_SQL));
        } catch (Exception e) {
            System.out.println(e.getMessage());
            throw new EIssueException(TDPLanguageUtils.getMessage
                    (TDPLanguageConstants.ERR_UNKNOW));
        }

        return PROCESS_STATE;
    }

}
